package top.wuyang.study.java24.dao;

import top.wuyang.study.java24.entity.TbFootballClub;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbFootballClubDAO {

    public static final String INSERT = "insert into tb_football_club(tname,league,intro,champions,cups,fans,goal,gname) values(?,?,?,?,?,?,?,?)";

    public static final String QUERY = "select id,tname,league,champions,cups,fans,goal,gname,create_time from tb_football_club";


    public static final String QUERY_BY_NAME = "select * from tb_football_club where tname = ?";

    public static final String QUERY_BY_ID = "select * from tb_football_club where id = ?";

    public static final String DELETE = "delete from tb_football_club where id = ?";
    public static final String UPDATE = "update tb_football_club set tname=?,league=?,intro=?,champions=?,cups=?,fans=?,goal=?,gname=? where id = ?";


    public static int insert(TbFootballClub football) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(INSERT);
        ps.setString(1, football.getTname());
        ps.setString(2, football.getLeague());
        ps.setString(3, football.getIntro());
        ps.setInt(4, football.getChampions());
        ps.setInt(5, football.getCups());
        ps.setInt(6, football.getFans());
        ps.setInt(7, football.getGoal());
        ps.setString(8, football.getGname());
        int i = ps.executeUpdate();
        conn.close();
        return i;


    }

    public static List<TbFootballClub> query() throws Exception {
        List<TbFootballClub> list = new ArrayList<>();
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            TbFootballClub football = new TbFootballClub();
            football.setId(rs.getInt("id"));
            football.setTname(rs.getString("tname"));
            football.setLeague(rs.getString("league"));
            football.setChampions(rs.getInt("champions"));
            football.setCups(rs.getInt("cups"));
            football.setFans(rs.getInt("fans"));
            football.setGoal(rs.getInt("goal"));
            football.setGname(rs.getString("gname"));
            football.setCreate_time(rs.getTimestamp("create_time"));
            list.add(football);
        }

        conn.close();
        return list;
    }

    public static TbFootballClub queryByName(String tname) throws Exception {
        TbFootballClub football = null;
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_NAME);
        ps.setString(1, tname);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            football = new TbFootballClub();
            football.setId(rs.getInt("id"));
            football.setTname(rs.getString("tname"));
            football.setLeague(rs.getString("league"));
            football.setChampions(rs.getInt("champions"));
            football.setCups(rs.getInt("cups"));
            football.setFans(rs.getInt("fans"));
            football.setGoal(rs.getInt("goal"));
            football.setGname(rs.getString("gname"));
            football.setCreate_time(rs.getTimestamp("create_time"));
            football.setIntro(rs.getString("intro"));
        }

        conn.close();
        return football;

    }

    public static TbFootballClub queryById(Integer id) throws Exception {
        TbFootballClub football = null;
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_ID);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            football = new TbFootballClub();
            football.setId(rs.getInt("id"));
            football.setTname(rs.getString("tname"));
            football.setLeague(rs.getString("league"));
            football.setChampions(rs.getInt("champions"));
            football.setCups(rs.getInt("cups"));
            football.setFans(rs.getInt("fans"));
            football.setGoal(rs.getInt("goal"));
            football.setGname(rs.getString("gname"));
            football.setCreate_time(rs.getTimestamp("create_time"));
            football.setIntro(rs.getString("intro"));
        }
        conn.close();
        return football;

    }

    public static int delete(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(DELETE);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    public static int update(TbFootballClub football) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        ps.setString(1, football.getTname());
        ps.setString(2, football.getLeague());
        ps.setString(3, football.getIntro());
        ps.setInt(4, football.getChampions());
        ps.setInt(5, football.getCups());
        ps.setInt(6, football.getFans());
        ps.setInt(7, football.getGoal());
        ps.setString(8, football.getGname());
        ps.setInt(9, football.getId());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }
}









